from initdatabase import *
from interactdb import *

def print_tmall_flagship_store_order_hour(start_date, end_date):  # 订单小时表

    # start_date = '2024-02-18 10:00'
    # end_date = '2024-02-18 11:00'
    df = pd.read_sql_query(
        f"SELECT * FROM tmall_flagship_store_order_hour WHERE order_creation_time BETWEEN '{start_date}' AND '{end_date}'",
        engine)

    if df.empty:
        return ['查询时间段内订单信息为空', pd.DataFrame()]
    df = df[['crawl_time', 'stat_time', 'order_number',
             'order_creation_time', 'good_name', 'good_id', 'good_link',
             'good_main_image', 'good_main_image_url', 'sixty_nine_code',
             'good_actual_price', 'good_trans_count',
             'good_series', 'good_product_combination',
             'good_giveaway_item_combination', 'good_giveaway_item_price',
             'good_pack_count', 'good_total_pieces', 'good_retail_price',
             'good_initial_price', 'good_giveaway_item_price_ratio',
             'good_big_promotion_price', 'good_small_promotion_price']]

    def tmall_flagship_store_order_stat_exe(df_order):

        def calculate_discount_fee_rate(row):
            try:
                return (row['good_initial_price'] - row['good_actual_price']) / row['good_initial_price']
            except ZeroDivisionError:
                return np.nan

        def calculate_discount_rate(row):
            try:
                return row['good_actual_price'] / row['good_retail_price']
            except ZeroDivisionError:
                return np.nan

        # 计算 discount_fee_rate
        # df_order['discount_fee_rate'] = df_order.apply(lambda x: calculate_discount_fee_rate(x), axis=1)
        df_order['折扣费率'] = df_order.apply(lambda x: calculate_discount_fee_rate(x), axis=1)

        # 计算 discount_rate
        # df_order['discount_rate'] = df_order.apply(lambda x: calculate_discount_rate(x), axis=1)
        df_order['折扣率'] = df_order.apply(lambda x: calculate_discount_rate(x), axis=1)

        # 计算 is_break_small_promotion
        # df_order['is_break_small_promotion'] = df_order.apply(lambda x: (x['good_actual_price'] < x['good_small_promotion_price']), axis=1)
        df_order['是否破日促'] = df_order.apply(lambda x: (x['good_actual_price'] < x['good_small_promotion_price']),
                                                axis=1)

        # 计算 is_break_big_promotion
        # df_order['is_break_big_promotion'] = df_order.apply(lambda x: (x['good_actual_price'] < x['good_big_promotion_price']), axis=1)
        df_order['是否破大促'] = df_order.apply(lambda x: (x['good_actual_price'] < x['good_big_promotion_price']),
                                                axis=1)

        # 计算 break_small_promotion_price_difference
        # df_order['break_small_promotion_price_difference'] = df_order.apply(lambda x: (x['good_actual_price'] - x['good_small_promotion_price']), axis=1)
        df_order['破日促价差'] = df_order.apply(lambda x: (x['good_actual_price'] - x['good_small_promotion_price']),
                                                axis=1)
        df['破日促价差'] = df['破日促价差'].apply(lambda x: '---' if x > 0 else '{:.2f}'.format(x))

        # 计算 break_big_promotion_price_difference
        # df_order['break_big_promotion_price_difference'] = df_order.apply(lambda x: (x['good_actual_price'] - x['good_big_promotion_price']), axis=1)
        df_order['破大促价差'] = df_order.apply(lambda x: (x['good_actual_price'] - x['good_big_promotion_price']),
                                                axis=1)
        df['破大促价差'] = df['破大促价差'].apply(lambda x: '---' if x > 0 else '{:.2f}'.format(x))

        return df_order

    df = tmall_flagship_store_order_stat_exe(df)
    df.rename(columns={'crawl_time': '爬取时间', 'stat_time': '统计时间', 'order_number': '订单号',
                       'order_creation_time': '订单创建时间', 'good_name': '商品名称', 'good_id': '商品ID',
                       'good_link': '商品链接', 'good_main_image': '商品主图', 'good_main_image_url': '商品主图链接',
                       'sixty_nine_code': '69码', 'good_actual_price': '实际到手价',
                       'good_trans_count': '商品数量', 'good_series': '商品系列', 'good_product_combination': '产品组合',
                       'good_giveaway_item_combination': '赠品明细', 'good_giveaway_item_price': '赠品价格',
                       'good_pack_count': '包数', 'good_total_pieces': '总片数', 'good_retail_price': '零售价',
                       'good_initial_price': '开单价', 'good_giveaway_item_price_ratio': '赠品费率',
                       'good_big_promotion_price': '大促底价', 'good_small_promotion_price': '日促底价'}, inplace=True)

    return ['成功', df]


def print_tmall_flagship_store_good_price_sales_hour(start_date, end_date):  # 订单小时波动监测表

    # start_date = '2024-02-18 10:00'
    # end_date = '2024-02-18 20:00'

    # 读入数据
    df = pd.read_sql_query(
        f"SELECT * FROM tmall_flagship_store_good_price_sales_hour WHERE create_time BETWEEN '{start_date}' AND '{end_date}'",
        engine)

    if df.empty:
        return ['查询时间段内订单信息为空', pd.DataFrame()]
    df = df[['create_time', 'stat_time', 'sixty_nine_code', 'good_id', 'good_name', 'good_link',
             'good_main_image', 'good_main_image_url', 'good_trans_count', 'good_min_actual_price',
             'good_max_actual_price', 'good_mean_actual_price', 'good_sales_change', 'good_min_actual_price_change',
             'good_max_actual_price_change', 'good_mean_actual_price_change']].copy()

    df.rename(columns={'create_time': '数据计算时间', 'stat_time': '数据统计时间', 'sixty_nine_code': '69码', 'good_id': '商品ID',
                       'good_name': '商品名称', 'good_link': '商品链接', 'good_main_image': '商品主图', 'good_main_image_url': '商品主图链接',
                       'good_trans_count': '销量', 'good_min_actual_price': '最低到手价', 'good_max_actual_price': '最高到手价',
                       'good_mean_actual_price': '平均到手价', 'good_sales_change': '销量变化', 'good_min_actual_price_change': '最低到手价变化',
                       'good_max_actual_price_change': '最高到手价变化', 'good_mean_actual_price_change': '平均到手价变化'},
              inplace=True)

    return ['成功', df]

# 返回结构：['', pd.Dataframe]